package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.entity.AuthStudentSubjectMap;
import com.orange.modules.entity.school_data.AuthStudentData;

@Repository
public class AuthStudentDataRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthStudentDataDao authStudentDataDao;
	
	@Autowired
	private AuthStudentSubjectMapDao authStudentSubjectMapDao;
	/**
	 * 新增学生数据
	 * @param asd
	 * @throws SQLException
	 */
	public void insertAuthStudentData(AuthStudentData asd) throws SQLException{
		authStudentDataDao.save(asd);
	}
	
	/**
	 * 新增学生学科信息
	 * @param assm
	 * @throws SQLException
	 */
	public void insertAuthStudentSubjectMap(AuthStudentSubjectMap assm) throws SQLException{
		authStudentSubjectMapDao.save(assm);
	}
	
	
	public AuthStudentData queryStudentDataBySchoolId(String schoolId ,String loginType) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_id as sid,t.s_graduated_students as graduatedStudents,t.s_graduated_undergraduate_major as graduatedUndergraduateMajor,"
				+ "t.s_graduated_specialized_subject as graduatedSpecializedSubject,t.s_school_all_students as schoolAllStudents,t.s_all_undergraduate_major as allUndergraduateMajor,"
				+ "t.s_all_specialized_subject as allSpecializedSubject,t.s_inschool_students as inschoolStudents,t.s_inschool_undergraduate_major as inschoolUndergraduateMajor,"
				+ "t.s_inschool_specialized_subject as inschoolSpecializedSubject,t.s_inschool_boy as inschoolboy,s_inschool_girl as inschoolgirl,t.s_teacher_qualification_certificate as teacherQualificationCertificate,"
				+ "t.s_relevant_educational_institutions as relevantEducationalInstitutions,t.s_employing_units as employingUnits,"
				+ "t.s_createtime as createtime,t.login_type as logintype,t.s_status as status,t.s_remark as remark FROM t_auth_student_data t WHERE t.s_id = ?";
		if(StringUtils.isNotBlank(loginType)){
			sql+="AND  t.login_type ='"+loginType+"'";
		}
		AuthStudentData asd = dbHelper.getBean(sql, AuthStudentData.class,schoolId);
		return asd;
	}
	
	public List<AuthStudentSubjectMap> queryStudentSubjectByStuId(String stuId) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.sd_id as sdid,t.s_subjectname as subjectname,t.s_createtime as createtime,"
				+ "t.s_gradenum_one as gradeNumOne,t.s_gradenum_two as gradeNumTwo,"
				+ "t.s_gradenum_three as gradeNumThree,t.s_gradenum_four as gradeNumFour,t.s_wholenum as wholeNum "
				+ "FROM t_auth_student_subject_map t WHERE t.sd_id = '"+stuId+"'";
		List<AuthStudentSubjectMap> list = dbHelper.getBeanList(sql, AuthStudentSubjectMap.class);
		return list;
	}
	
	public void deleteAuthStudentSubject(String stuId) throws SQLException{
		String sql = "DELETE FROM t_auth_student_subject_map WHERE sd_id=?";
		dbHelper.execute(sql,stuId);
	}
}
